package com.icss.struct.dao;

import com.icss.struct.dto.Tag;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

public class SNSTagDao {
    public List<Tag> findTop10() {
        List<Tag> tlist = new ArrayList<>();
        // 2. 创建链接
        Connection conn = null;
        try {
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mytest?characterEncoding=utf8", "root", "root");

            // 3. 创建执行器：
            String sql = "SELECT t1.id, t1.tagname, t1.img, t1.refcount,\n" +
                    "t1.rescount, t1.sort    \n" +
                    "FROM sns_tag t1\n" +
                    "ORDER BY t1.sort LIMIT 0, 10";

            PreparedStatement ps = conn.prepareStatement(sql);

            // 4. 执行SQl,处理结果
            ResultSet rs = ps.executeQuery();

            // 处理结果集
            while (rs.next()) { // 有下一条记录并且指向这一行
                Tag item= new Tag();
                item.setId(rs.getInt(1));
                item.setTagname(rs.getString("tagname"));
                item.setImage(rs.getString("img"));
                item.setRefcount(rs.getInt(4));
                item.setRescount(rs.getInt(5));
                item.setSort(rs.getInt(6));
                tlist.add(item);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {

            // 5. 关闭资源
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        return tlist;
    }

    public void updateById(HashMap<String, String> param) {
        // 2. 创建链接
        Connection conn = null;
        try {
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mytest?characterEncoding=utf8", "root", "root");
            conn.setAutoCommit(false);
            // 3. 创建执行器：
            String sql = "UPDATE sns_tag SET tagname = ?, refcount=?\n" +
                    ",rescount= ?, sort = ?\n" +
                    "WHERE id = ?";

            PreparedStatement ps = conn.prepareStatement(sql);

            ps.setString(1, param.get("tagname"));
            ps.setString(2, String.valueOf(param.get("refcount")));
            ps.setString(3, String.valueOf(param.get("rescount")));
            ps.setString(4, String.valueOf(param.get("sort")));
            ps.setString(5, String.valueOf(param.get("id")));
            // 4. 执行SQl,处理结果
            int r = ps.executeUpdate();
            if (r == 1) {
                conn.commit();
            } else {
                conn.rollback();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {

            // 5. 关闭资源
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }


    }
}
